import pandas
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

from file import get_serial_number


sheet_name = ['商贸管理学院', '通信与电子工程学院', '人文与社会科学学院', '智能制造工程学院']


def change_start(name):
    wb2 = load_workbook('template.xlsx')
    for sheet in sheet_name:
        ws2 = wb2.get_sheet_by_name(sheet)
        cell2 = ws2["A1"]
        cell2.value = '上海科学技术职业学院_' + name + '青年大学习'
        ws2.merge_cells("A1:G1")
        wb2.save('template.xlsx')


def center():
    align = Alignment(horizontal='center', vertical='center')
    wb = load_workbook('template.xlsx')
    for sheet in sheet_name:
        ws = wb.get_sheet_by_name(sheet)
        max_rows = ws.max_row
        max_columns = ws.max_column
        for i in range(1, max_rows + 1):
            for j in range(1, max_columns + 1):
                ws.cell(i, j).alignment = align
    wb.save('template.xlsx')


def correction():
    wb = load_workbook('./input/input.xlsx')
    ws = wb.active
    ws.delete_cols(1, 1)
    ws.delete_rows(1, 2)
    ws.insert_rows(1, 1)
    row = ws.max_row
    ws.delete_rows(row)
    for i in range(1, 8):
        index = ws.cell(1, i)
        if i == 1:
            index.value = '课程'
        elif i == 2:
            index.value = '一级团组织'
        elif i == 3:
            index.value = '二级团组织'
        elif i == 4:
            index.value = '三级团组织'
        elif i == 5:
            index.value = '单位/班级/社区（村）'
        elif i == 6:
            index.value = '学号/卡号/工号'
        elif i == 7:
            index.value = '学习时间'
    wb.save("./input/input.xlsx")
    print('文件修正成功')


def filtration():
    df = pandas.read_excel('./input/input.xlsx')
    grouped = df.groupby('单位/班级/社区（村）', as_index=True, sort=False)
    wb = load_workbook('template.xlsx')
    for name, group in grouped:
        if name == '商贸管理学院':
            result = group.sort_values(by=['学号/卡号/工号'], ascending=True)
            ws = wb['商贸管理学院']
            for r in dataframe_to_rows(result, index=False, header=False):
                ws.append(r)
            wb.save('template.xlsx')
        elif name == '通信与电子工程学院':
            result = group.sort_values(by=['学号/卡号/工号'], ascending=True)
            ws = wb['通信与电子工程学院']
            for r in dataframe_to_rows(result, index=False, header=False):
                ws.append(r)
            wb.save('template.xlsx')
        elif name == '人文与社会科学学院':
            result = group.sort_values(by=['学号/卡号/工号'], ascending=True)
            ws = wb['人文与社会科学学院']
            for r in dataframe_to_rows(result, index=False, header=False):
                ws.append(r)
            wb.save('template.xlsx')
        elif name == '智能制造工程学院':
            result = group.sort_values(by=['学号/卡号/工号'], ascending=True)
            ws = wb['智能制造工程学院']
            for r in dataframe_to_rows(result, index=False, header=False):
                ws.append(r)
            wb.save('template.xlsx')
        else:
            wbe = load_workbook('error.xlsx')
            result = group.sort_values(by=['学号/卡号/工号'], ascending=True)
            ws = wbe['未知']
            for r in dataframe_to_rows(result, index=False, header=False):
                ws.append(r)
            wbe.save('error.xlsx')
    change_start(get_serial_number())
    center()
    wb.save("./input/input.xlsx")
    del df
    print('文件写入成功')
